﻿using System;
using System.Collections.Generic;
using System.Linq;
using System.Text;
using System.Data.SqlClient;

namespace TravelExpertsManagementConsole
{
    class SupplierContactDataAccess
    {
        private SqlConnection ConnectionDB;
        private SqlCommand CommandDB;

        public SupplierContactDataAccess()
        {
            ConnectionDB = new SqlConnection("server=(local);database=TravelExperts; integrated security=SSPI");
            CommandDB = new SqlCommand();
            CommandDB.Connection = ConnectionDB;
        }

        public List<SupplierContact> Select() //Selects the information from DataBase
        {
            List<SupplierContact> itsContact = new List<SupplierContact>();
            CommandDB.CommandText = "SELECT * FROM Affiliations, SupplierContacts";

            try
            {
                ConnectionDB.Open();
                SqlDataReader Reader = CommandDB.ExecuteReader();

                while (Reader.Read())
                {
                    SupplierContact SupCon = new SupplierContact();

                    //need if statements because of NULL values in tables

                    if (!Reader.IsDBNull(7))
                    {
                        SupCon.SupConAddress = Reader.GetString(7);
                    }

                    if (!Reader.IsDBNull(12))
                    {
                        SupCon.SupConBusPhone = Reader.GetString(12);
                    }

                    if (!Reader.IsDBNull(8))
                    {
                        SupCon.SupConCity = Reader.GetString(8);
                    }

                    if (!Reader.IsDBNull(6))
                    {
                        SupCon.SupConCompany = Reader.GetString(6);
                    }

                    if (!Reader.IsDBNull(11))
                    {
                        SupCon.SupConCountry = Reader.GetString(11);
                    }

                    if (!Reader.IsDBNull(14))
                    {
                        SupCon.SupConEmail = Reader.GetString(14);
                    }

                    if (!Reader.IsDBNull(13))
                    {
                        SupCon.SupConFax = Reader.GetString(13);
                    }

                    if (!Reader.IsDBNull(4))
                    {
                        SupCon.SupConFirstName = Reader.GetString(4);
                    }

                    if (!Reader.IsDBNull(5))
                    {
                        SupCon.SupConLastName = Reader.GetString(5);
                    }

                    if (!Reader.IsDBNull(10))
                    {
                        SupCon.SupConPostal = Reader.GetString(10);
                    }

                    if (!Reader.IsDBNull(9))
                    {
                        SupCon.SupConProv = Reader.GetString(9);
                    }

                    if (!Reader.IsDBNull(15))
                    {
                        SupCon.SupConURL = Reader.GetString(15);
                    }

                    if (!Reader.IsDBNull(3))
                    {
                        SupCon.SupplierContactId = Reader.GetInt32(3);
                    }

                    if (!Reader.IsDBNull(2))
                    {
                        SupCon.AffDesc = Reader.GetString(2);
                    }

                    if (!Reader.IsDBNull(0))
                    {
                        SupCon.AffiliationId = Reader.GetString(0);
                    }

                    if (!Reader.IsDBNull(1))
                    {
                        SupCon.AffName = Reader.GetString(1);
                    }

                    itsContact.Add(SupCon); //Adding to the list
                }
            }
            catch (SqlException ex)
            {
                throw ex;
            }
            finally
            {
                ConnectionDB.Close();
            }

            return itsContact;
        }

        public void Adding(SupplierContact SupCon) //Insert information in the Database
        {
            CommandDB.CommandText = "INSERT INTO SupplierContacts, Affiliations VALUES (@SupplierContactId, @SupConFirstName, @SupConLastName, @SupConCompany, @SupConAddress, @SupConCity, @SupConProv, @SupConPostal, @SupConCountry, @SupConBusPhone, @SupConFax, @SupConEmail, @SupConURL, @AffiliationId, @AffDesc, @AffName";
            ExecuteNonRead(SupCon);
        }

        public void UpDate(SupplierContact SupCon) //UpDates information in the DataBase
        {
            CommandDB.CommandText = "UPDATE SupplierContacts, Affiliations SET (ContactId=@SupplierContactId, FirstName=@SupConFirstName, LastName=@SupConLastName, CompanyName=@SupConCompany, Address=@SupConAddress, City=@SupConCity, Province=@SupConProv, PostalCode=@SupConPostal, Country=@SupConCountry, PhoneNumber@SupConBusPhone, FaxNumber=@SupConFax, Email=@SupConEmail, URL=@SupConURL, AffID=@AffiliationId, AffDesc=@AffDesc, AffName=@AffName";
            ExecuteNonRead(SupCon);
        }

        public void Delete(SupplierContact SupCon) //Deletes an object from the Database
        {
            CommandDB.CommandText = "DELETE FROM Affiliations, SupplierContact WHERE ContactId=@SupplierContactId, AffID=@AffiliationId";
            ExecuteNonRead(SupCon);
        }

        public void ExecuteNonRead(SupplierContact SupCon) //Method 
        {
            CommandDB.Parameters.AddWithValue("@SupplierContactId", SupCon.SupplierContactId);
            CommandDB.Parameters.AddWithValue("@SupConFirstName", SupCon.SupConFirstName);
            CommandDB.Parameters.AddWithValue("@SupConLastName", SupCon.SupConLastName);
            CommandDB.Parameters.AddWithValue("@SupConCompany", SupCon.SupConCompany);
            CommandDB.Parameters.AddWithValue("@SupConAddress", SupCon.SupConAddress);
            CommandDB.Parameters.AddWithValue("@SupConCity", SupCon.SupConCity);
            CommandDB.Parameters.AddWithValue("@SupConProv", SupCon.SupConProv);
            CommandDB.Parameters.AddWithValue("@SupConPostal", SupCon.SupConPostal);
            CommandDB.Parameters.AddWithValue("@SupConCountry", SupCon.SupConCountry);
            CommandDB.Parameters.AddWithValue("@SupConBusPhone", SupCon.SupConBusPhone);
            CommandDB.Parameters.AddWithValue("@SupConFax", SupCon.SupConFax);
            CommandDB.Parameters.AddWithValue("@SupConEmail", SupCon.SupConEmail);
            CommandDB.Parameters.AddWithValue("@SupConURL", SupCon.SupConURL);
            CommandDB.Parameters.AddWithValue("@AffiliationId", SupCon.AffiliationId);
            CommandDB.Parameters.AddWithValue("@AffName", SupCon.AffName);
            CommandDB.Parameters.AddWithValue("@AffDesc", SupCon.AffDesc);

            try
            {
                ConnectionDB.Open();
                CommandDB.ExecuteNonQuery();
            }
            catch (SqlException ex)
            {
                throw ex;
            }
            finally
            {
                ConnectionDB.Close();
            }
        }
    }
}
